//    The page gives admin the functionality to Upload the Hospital's Data from a file(XLS)         
//    The data will be inserted/updated to the tbl_hospitals,tbl_hospital_speciality tables.                                
//    Author: Rupinder Saini                                                                    
//    Date: May 03,2006    


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using SiteManager;
using System.Data.SqlClient;

public partial class Admin_Ad_UploadHospitalsFile : System.Web.UI.Page
{// creating an instance of manager class
    Manager mgr = new Manager(true,"ConnectionString");
    public DataTable dt_Err;
    public DataSet ds_spe = new DataSet();

    protected void Page_Load(object sender, EventArgs e)
    {
        lblMsg.Text = "";
        ds_spe = mgr.ExecuteQuery("select Id,Name from tbl_Speciality", "tbl_Speciality");
    }

    /// Uploading the file to Temporary folder in the server and reading its data and if the CSV contains
    /// correct data then save the data in the DataBase else show the error.

    #region uploadbutton
    protected void btnUpload_Click(object sender, EventArgs e)
    {


        if (uplFileUpload.HasFile)
        {
            string contentType = uplFileUpload.PostedFile.ContentType;
            string filename = uplFileUpload.PostedFile.FileName;

            // Checking the data for the uploaded file , should be Excel only
            if ((contentType.Equals("application/octet-stream") || contentType.Equals("application/excel") || contentType.Equals("application/vnd.ms-excel") || contentType.Equals("application/x-excel") || contentType.Equals("application/x-msexcel")) && filename.Substring(filename.LastIndexOf('.') + 1).ToUpper().Equals("XLS"))
            {
                // checking whether the file has no data 
                if (uplFileUpload.PostedFile.ContentLength > 0)
                {
                    try
                    {
                        // saving the file to the Temporary folder
                        uplFileUpload.SaveAs(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        ExcelReader exReader = new ExcelReader(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        string sheetName = exReader.GetExcelSheetNames()[0];
                        //checking for the correct data and if data is error free inserting it into the Database
                        try
                        {
                            CheckDataToTables(exReader.GetDataTable(sheetName));

                        }
                        catch (Exception es)
                        {
                            lblMsg.ForeColor = System.Drawing.Color.Blue;
                            lblMsg.Text = es.Message;
                        }

                    }
                    catch (Exception ex)
                    {
                        lblMsg.Text = ex.Message;
                        lblMsg.ForeColor = System.Drawing.Color.Red;
                        return;
                    }
                    finally
                    {
                        //Delete File
                        System.IO.FileInfo fi = new System.IO.FileInfo(Server.MapPath(@".\TemporaryData\") + uplFileUpload.FileName);
                        fi.Delete();
                    }
                }
                else
                {
                    lblMsg.Text = "File contains no data!!";
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    return;
                }
            }
            else
            {
                lblMsg.Text = "InCorrect File Type. Please select only XLS file to upload!!";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
        }
        else
        {
            lblMsg.Text = "No uploaded file!!";
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return;
        }
    }

    #endregion


    //This functiom reads in data from the DataTable for Inserting into the DataBase 
    #region CheckDataToTables
    private void CheckDataToTables(DataTable dt)
    {
        // Table to store error Records in the tables columns if any        
        dt_Err = new DataTable();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            dt_Err.Columns.Add(dt.Columns[i].ColumnName, Type.GetType("System.String"));
            //dt_Err.Columns.Add(dt.Columns[i].ColumnName, Type.GetType("System.String"));
        }
        bool IsError = false;

        if (dt.Columns.Count == 33)
        {
            //reading data for each row of the DataTable (from xls) 
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    bool IsInsertRow = false;
                    //DataRow HosRow = dt.Rows[i];
                   // for(int column=0;column<dt.Rows[i].
                     DataRow HosRow = dt_Err.NewRow();
                     for (int col = 0; col < dt.Columns.Count; col++)
                     {
                         HosRow[col] = dt.Rows[i][col];
                     }
                   // check if either overall or any other rating is given
                    char[] RChr = { '%', '-' };
                    string toverall = HosRow["Overall Rating"].ToString().Trim().Replace("'", "''").TrimEnd(RChr);
                    if (toverall.Trim().Length > 0)
                    {
                        // overall rating exist
                    }
                    else
                    {
                        int count = 0;
                        for (int iC = 2; iC < dt.Columns.Count; iC++)
                        {
                            string colval = HosRow[iC].ToString().Trim().Replace("'", "''").TrimEnd(RChr);
                                //dt.Columns[iC].ToString().Trim().Replace("'", "''").TrimEnd(RChr);
                            if (colval.Trim().Length > 0)
                            {
                                // any other speciality id value exist
                            }
                            else
                            {
                                count = count + 1;
                            }
                        }
                        if (count == 31) // neither overall or any other rating exist
                        {
                           IsInsertRow = true;
                           HosRow.SetColumnError("Overall Rating", "Either Overall or any other rating must be provided.");
                           HosRow["Overall Rating"] = "<font color='red'> " + HosRow["Overall Rating"] + "</font>";
                        }

                    }
    



 # region get each columns the value in varibales
                    if (!IsInsertRow)
                    {
                        char[] RChar = { '%', '-' };
                        string tHName = HosRow["Hospital Name"].ToString().Trim().Replace("'", "''");
                        string tRating = HosRow["Overall Rating"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tAllergy = HosRow["Allergy & Immunology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tAnesthes = HosRow["Anesthesiology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tCardio = HosRow["Cardiology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tDerma = HosRow["Dermatology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tEMed = HosRow["Emergency Medicine"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tEndo = HosRow["Endocrinology, Diabetes, & Metabolism"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tFamily = HosRow["Family Practice"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tGastro = HosRow["Gastroenterology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tGen = HosRow["General Practice"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tGeri = HosRow["Geriatrics"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tInfect = HosRow["Infectious Disease"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tIMed = HosRow["Internal Medicine"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tMedG = HosRow["Medical Genetics"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tNep = HosRow["Nephrology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tNSur = HosRow["Neurological Surgery"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tNeuro = HosRow["Neurology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tObs = HosRow["Obstetrics & Gynecology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tOnco = HosRow["Oncology (Cancer)"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tOphtha = HosRow["Ophthalmology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tOrtho = HosRow["Orthopedics"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tOther = HosRow["Other"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tOto = HosRow["Otolaryngology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tPatho = HosRow["Pathology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tPed = HosRow["Pediatrics"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tPhyMed = HosRow["Physical Medicine & Rehabilitation"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tPSur = HosRow["Plastic Surgery"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tPMed = HosRow["Preventive Medicine"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tPsy = HosRow["Psychiatry"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tRadio = HosRow["Radiology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tSur = HosRow["Surgery"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tUro = HosRow["Urology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);

 #endregion


                        //checking for errors in the variables and setting the suitable error message in the corrresponding Column
                        # region checking for errors in the varibales and setting the suitable error message in the corrresponding Column

                        if (checkLength_DataType(tHName, "string", 500, 1).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Hospital Name", checkLength_DataType(tHName, "string", 500, 1));
                            HosRow["Hospital Name"] = "<font color='red'>" + HosRow["Hospital Name"] + "</font>";
                        }
                        //else  // checking for duplicates
                        //{
                        //    if (IsExistHospital(tHName))
                        //    {
                        //        IsInsertRow = true;
                        //        HosRow.SetColumnError("Hospital Name", "The Hospital Name already exist in the database!!Please try with a different name.");
                        //        HosRow["Hospital Name"] = "<font color='red'>" + HosRow["Hospital Name"] + "</font>";
                        //    }
                        //}


                        if (checkLength_DataType(tRating, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Overall Rating", checkLength_DataType(tRating, "decimal", 0, 0));
                            HosRow["Overall Rating"] = "<font color='red'>" + HosRow["Overall Rating"] + "</font>";
                        }

                        if (checkLength_DataType(tAllergy, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Allergy & Immunology", checkLength_DataType(tAllergy, "decimal", 0, 0));
                            HosRow["Allergy & Immunology"] = "<font color='red'>" + HosRow["Allergy & Immunology"] + "</font>";
                        }


                        if (checkLength_DataType(tAnesthes, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Anesthesiology", checkLength_DataType(tAnesthes, "decimal", 0, 0));
                            HosRow["Anesthesiology"] = "<font color='red'>" + HosRow["Anesthesiology"] + "</font>";
                        }

                        if (checkLength_DataType(tCardio, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Cardiology", checkLength_DataType(tCardio, "decimal", 0, 0));
                            HosRow["Cardiology"] = "<font color='red'>" + HosRow["Cardiology"] + "</font>";
                        }

                        if (checkLength_DataType(tDerma, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Dermatology", checkLength_DataType(tDerma, "decimal", 0, 0));
                            HosRow["Dermatology"] = "<font color='red'>" + HosRow["Dermatology"] + "</font>";
                        }
                        if (checkLength_DataType(tEMed, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Emergency Medicine", checkLength_DataType(tEMed, "decimal", 0, 0));
                            HosRow["Emergency Medicine"] = "<font color='red'>" + HosRow["Emergency Medicine"] + "</font>";
                        }

                        if (checkLength_DataType(tEndo, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Endocrinology, Diabetes, & Metabolism", checkLength_DataType(tEndo, "decimal", 0, 0));
                            HosRow["Endocrinology, Diabetes, & Metabolism"] = "<font color='red'>" + HosRow["Endocrinology, Diabetes, & Metabolism"] + "</font>";
                        }

                        if (checkLength_DataType(tFamily, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Family Practice", checkLength_DataType(tFamily, "decimal", 0, 0));
                            HosRow["Family Practice"] = "<font color='red'>" + HosRow["Family Practice"] + "</font>";
                        }

                        if (checkLength_DataType(tGastro, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Gastroenterology", checkLength_DataType(tGastro, "decimal", 0, 0));
                            HosRow["Gastroenterology"] = "<font color='red'>" + HosRow["Gastroenterology"] + "</font>";
                        }

                        if (checkLength_DataType(tGen, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("General Practice", checkLength_DataType(tGen, "decimal", 0, 0));
                            HosRow["General Practice"] = "<font color='red'>" + HosRow["General Practice"] + "</font>";
                        }

                        if (checkLength_DataType(tGeri, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Geriatrics", checkLength_DataType(tGeri, "decimal", 0, 0));
                            HosRow["Geriatrics"] = "<font color='red'>" + HosRow["Geriatrics"] + "</font>";
                        }

                        if (checkLength_DataType(tInfect, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Infectious Disease", checkLength_DataType(tInfect, "decimal", 0, 0));
                            HosRow["Infectious Disease"] = "<font color='red'>" + HosRow["Infectious Disease"] + "</font>";
                        }

                        if (checkLength_DataType(tIMed, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Internal Medicine", checkLength_DataType(tIMed, "decimal", 0, 0));
                            HosRow["Internal Medicine"] = "<font color='red'>" + HosRow["Internal Medicine"] + "</font>";
                        }

                        if (checkLength_DataType(tMedG, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Medical Genetics", checkLength_DataType(tMedG, "decimal", 0, 0));
                            HosRow["Medical Genetics"] = "<font color='red'>" + HosRow["Medical Genetics"] + "</font>";
                        }

                        if (checkLength_DataType(tNep, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Nephrology", checkLength_DataType(tNep, "decimal", 0, 0));
                            HosRow["Nephrology"] = "<font color='red'>" + HosRow["Nephrology"] + "</font>";
                        }

                        if (checkLength_DataType(tNSur, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Neurological Surgery", checkLength_DataType(tNSur, "decimal", 0, 0));
                            HosRow["Neurological Surgery"] = "<font color='red'>" + HosRow["Neurological Surgery"] + "</font>";
                        }

                        if (checkLength_DataType(tNeuro, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Neurology", checkLength_DataType(tDerma, "decimal", 0, 0));
                            HosRow["Neurology"] = "<font color='red'>" + HosRow["Neurology"] + "</font>";
                        }


                        if (checkLength_DataType(tObs, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Obstetrics & Gynecology", checkLength_DataType(tObs, "decimal", 0, 0));
                            HosRow["Obstetrics & Gynecology"] = "<font color='red'>" + HosRow["Obstetrics & Gynecology"] + "</font>";
                        }
                        if (checkLength_DataType(tOnco, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Oncology (Cancer)", checkLength_DataType(tOnco, "decimal", 0, 0));
                            HosRow["Oncology (Cancer)"] = "<font color='red'>" + HosRow["Oncology (Cancer)"] + "</font>";
                        }
                        if (checkLength_DataType(tOphtha, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Ophthalmology", checkLength_DataType(tOphtha, "decimal", 0, 0));
                            HosRow["Ophthalmology"] = "<font color='red'>" + HosRow["Ophthalmology"] + "</font>";
                        }
                        if (checkLength_DataType(tOrtho, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Orthopedics", checkLength_DataType(tOrtho, "decimal", 0, 0));
                            HosRow["Orthopedics"] = "<font color='red'>" + HosRow["Orthopedics"] + "</font>";
                        }
                        if (checkLength_DataType(tOther, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Other", checkLength_DataType(tOther, "decimal", 0, 0));
                            HosRow["Other"] = "<font color='red'>" + HosRow["Other"] + "</font>";
                        }
                        if (checkLength_DataType(tOto, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Otolaryngology", checkLength_DataType(tOto, "decimal", 0, 0));
                            HosRow["Otolaryngology"] = "<font color='red'>" + HosRow["Otolaryngology"] + "</font>";
                        }
                        if (checkLength_DataType(tPatho, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Pathology", checkLength_DataType(tPatho, "decimal", 0, 0));
                            HosRow["Pathology"] = "<font color='red'>" + HosRow["Pathology"] + "</font>";
                        }
                        if (checkLength_DataType(tPed, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Pediatrics", checkLength_DataType(tPed, "decimal", 0, 0));
                            HosRow["Pediatrics"] = "<font color='red'>" + HosRow["Pediatrics"] + "</font>";
                        }
                        if (checkLength_DataType(tPhyMed, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Physical Medicine & Rehabilitation", checkLength_DataType(tPhyMed, "decimal", 0, 0));
                            HosRow["Physical Medicine & Rehabilitation"] = "<font color='red'>" + HosRow["Physical Medicine & Rehabilitation"] + "</font>";
                        }
                        if (checkLength_DataType(tPSur, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Plastic Surgery", checkLength_DataType(tPSur, "decimal", 0, 0));
                            HosRow["Plastic Surgery"] = "<font color='red'>" + HosRow["Plastic Surgery"] + "</font>";
                        }
                        if (checkLength_DataType(tPMed, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Preventive Medicine", checkLength_DataType(tPMed, "decimal", 0, 0));
                            HosRow["Preventive Medicine"] = "<font color='red'>" + HosRow["Preventive Medicine"] + "</font>";
                        }
                        if (checkLength_DataType(tPsy, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Psychiatry", checkLength_DataType(tPsy, "decimal", 0, 0));
                            HosRow["Psychiatry"] = "<font color='red'>" + HosRow["Psychiatry"] + "</font>";
                        }
                        if (checkLength_DataType(tRadio, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Radiology", checkLength_DataType(tRadio, "decimal", 0, 0));
                            HosRow["Radiology"] = "<font color='red'>" + HosRow["Radiology"] + "</font>";
                        }

                        if (checkLength_DataType(tSur, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Surgery", checkLength_DataType(tSur, "decimal", 0, 0));
                            HosRow["Surgery"] = "<font color='red'>" + HosRow["Surgery"] + "</font>";
                        }
                        if (checkLength_DataType(tUro, "decimal", 0, 0).Length > 0)
                        {
                            IsInsertRow = true;
                            HosRow.SetColumnError("Urology", checkLength_DataType(tUro, "decimal", 0, 0));
                            HosRow["Urology"] = "<font color='red'>" + HosRow["Urology"] + "</font>";
                        }
                    }

 #endregion
                   
                  
                    if (IsInsertRow)
                    {
                        //dt_Err.ImportRow(HosRow);
                        dt_Err.Rows.Add(HosRow);
                        IsError = true;
                    }
                }

                #region Final Action
                if (IsError)
                {
                    //Show the rows containing errors in the DataGrid  
                    grdErrors.Visible = true;
                    grdErrors.DataSource = dt_Err;
                    grdErrors.DataBind();

                    lblMsg.Text = "Some rows in the XLS file contains errors!!<br>Please see the error rows (Columns in red color) in the listing below.<br>To view error details place your mouse over the red area<br>Please correct the errors and upload the file again.";
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    return;
                }
                else
                {
                    //Insert the records into database
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow HosRow = dt.Rows[i];
                        char[] RChar = { '%','-' };
                        string tHName = HosRow["Hospital Name"].ToString().Trim().Replace("'", "''");
                        string tRating = HosRow["Overall Rating"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                        string tAllergy = HosRow["Allergy & Immunology"].ToString().Trim().Replace("'", "''").TrimEnd(RChar);

                        //Get the corresponding ADOCRating id for US rating
                        decimal dUSRating = GetADOCRating(tRating);
                        
                        SqlParameter[] sqlParam = new SqlParameter[2];
                        sqlParam[0] = new SqlParameter("@hname", SqlDbType.VarChar, 500);
                        sqlParam[1] = new SqlParameter("@rating", SqlDbType.Decimal);
                        sqlParam[1].Precision = 18;
                        sqlParam[1].Scale = 6;
                        sqlParam[0].Value = tHName;
                        sqlParam[1].Value = dUSRating;


                        DataSet ds_id = new DataSet();
                        try
                        {
                            ds_id = mgr.ExecuteQuery("insert_hospital", sqlParam, "tbl_hos");
                           
                            for (int i1 = 2; i1 < dt.Columns.Count; i1++)
                            {
                               string colvalue=HosRow[i1].ToString().Trim().Replace("'", "''").TrimEnd(RChar);
                               if (colvalue.Length > 0)
                               { // get the speciality id
                                   DataRow[] rr = ds_spe.Tables[0].Select("Name='" + dt.Columns[i1].ColumnName.ToString().Trim().Replace("'", "''") + "'");
                                   int len = rr.Length;
                               
                               if (len > 0) // speciality exists
                               {
                                   decimal USRating = GetADOCRating(colvalue);
                                   SqlParameter[] sqlParam1 = new SqlParameter[3];
                                   sqlParam1[0] = new SqlParameter("@h_id", SqlDbType.Int);
                                   sqlParam1[1] = new SqlParameter("@s_id", SqlDbType.Int);
                                   sqlParam1[2] = new SqlParameter("@score", SqlDbType.Decimal);
                                   sqlParam1[2].Precision = 18;
                                   sqlParam1[2].Scale = 6;
                                   sqlParam1[0].Value = Convert.ToInt32(ds_id.Tables[0].Rows[0]["h_id"].ToString());
                                   sqlParam1[1].Value = rr[0]["id"];
                                   sqlParam1[2].Value = USRating;
                                   try
                                   {
                                       mgr.ExecuteUpdate("insert_hospital_speciality", sqlParam1);
                                   }
                                   catch (Exception ex)
                                   {
                                       lblMsg.Text = ex.ToString();
                                       lblMsg.ForeColor = System.Drawing.Color.DarkMagenta;
                                       return;
                                   }
                               }

                               }
                            }
                        }
                        catch (Exception ex)
                        {
                            lblMsg.Text = ex.ToString();
                            lblMsg.ForeColor = System.Drawing.Color.DarkOrange;
                            return;
                        }
       
                    }
                    lblMsg.Text = "File " + uplFileUpload.FileName.ToString() + " uploaded successfully..";
                    lblMsg.ForeColor = System.Drawing.Color.Green;
                    grdErrors.Visible = false;
                }

            }
                # endregion

            else
            {
                //No rows found in the table
                lblMsg.Text = "No Data!!";
                lblMsg.ForeColor = System.Drawing.Color.Red;
                return;
            }
        }
        else
        {
            //Wrong no. of Columns
            lblMsg.Text = "Wrong no. of Columns!!";
            lblMsg.ForeColor = System.Drawing.Color.Red;
            return;
        }

    }
    #endregion

    #region general functions

    /// <summary>
    /// To check for duplicate hospital name
    /// </summary>
    /// <param name="inputname"></param>
    /// <returns></returns>
    /// 
    public bool IsExistHospital(String inputname)
    {
         string sql = "select count(*) from tbl_hospitals where HName='" + inputname.Trim() + "' and isdelete=0";
        DataSet ds = mgr.ExecuteQuery(sql, "tbl");
        int count = (int)ds.Tables[0].Rows[0][0];
        if (count > 0)
            return true;
        else
            return false;
    }

    

    /// <summary>
    /// To convert US rating to ADOC rating
    /// </summary>
    /// <param name="USrating"></param>
    /// <returns></returns>

    private decimal GetADOCRating(string USrating)
    {
        if (USrating.Trim().Length <= 0)
        {
            return 0;
        }
        else
        {
            // FindAdoc = (Weight - base score) * US Rating + Base Score
            return ((Convert.ToDecimal(20.00 - 13.33) * Convert.ToDecimal(USrating) / Convert.ToDecimal(100) + Convert.ToDecimal(13.33)));
            
        }

    }

   
    /// <summary>
    /// check datatype, etc. of a column
    /// </summary>
    /// <param name="var"></param>
    /// <param name="dataType"></param>
    /// <param name="maxLen"></param>
    /// <param name="required"></param>
    /// <returns></returns>

    private string checkLength_DataType(string var, string dataType, int maxLen, int required)
    {
        if (dataType == "string")
        {
            if (required == 1)
            {
                if (var.Trim().Length <= 0)
                    return "The value of this column can not be empty!!";
            }

            if (var.Length > maxLen)
                return "Maximium length of this field should be <=" + maxLen.ToString();
        }
        else if (var.Trim().Length>0)
        {
            int len = var.Trim().Length;
            if (dataType == "decimal")
            {
               try
                {
                    decimal test = Convert.ToDecimal(var);
                }
                catch
                {
                    return " DataType MisMatch. Decimal Type is required!!";
                }
            
           }
        }
        return "";
    }


    #endregion

    protected void grdErrors_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            for (int i = 0; i < e.Row.Cells.Count; i++)
            {
                e.Row.Cells[i].Text = Server.HtmlDecode(e.Row.Cells[i].Text);

                try
                {
                    //displaying the error messages tooltip for the rows,columns

                    e.Row.Cells[i].ToolTip = dt_Err.Rows[e.Row.RowIndex].GetColumnError(i);
                    if (dt_Err.Rows[e.Row.RowIndex].GetColumnError(i).Equals("Either Overall or any other rating must be provided."))
                    {
                        e.Row.Cells[i].BackColor = System.Drawing.Color.Red;
                        e.Row.Cells[i].ForeColor = System.Drawing.Color.Red;
                    }

                }
                catch { }
            }
        }
    }
}
